IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GrantCOIAccessOnUserLoad]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GrantCOIAccessOnUserLoad]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************            
* Name:   GrantCOIAccessOnUserLoad            
* Purpose:  Grants the User in the User hieararchy map table with COI access.
			
* NOte: The access will be granted only for users with fwkDomainUserId
	(if the user is not registered as the application user,then the same will be registered as application user.            
* PARAMETERS(IN)            
* Name          Description    
* @GrantParent	Determines whether to grant access for Parent/child in the COI_Hierarchy_Map table  
		1--> will grant access to users in the ParenTId column      
		0--> will grant access to users in the childId column     
* -------------      -------------------------------------------*/      
CREATE procedure GrantCOIAccessOnUserLoad(@GrantParent bit)
AS BEGIN
SET NOCOUNT ON

		Declare @GrantAccessQualifyTable as table(FwkDomainUserId int,PersonId int)
		BEGIN
		Insert into @GrantAccessQualifyTable Select Distinct adp.[FwkDomainUserId],adp.[Id] from admperson adp
		inner join [COI_Hierarchy_Map]chm
		on adp.[Id]=case when @GrantParent=1
					then chm.[ParentId]
					else chm.[ChildId]
					End
		and adp.[FwkDomainUserId]is not null
		and adp.AdmPersonTypeId=1

		Declare @childId as integer
		Declare @fwkDomainUserId as integer    
		    
		Set @childId= Null
		Set @fwkDomainUserId = null

		 DECLARE hierarchyCursor CURSOR FOR    
		 select [FwkDomainUserId],[PersonId] from @GrantAccessQualifyTable       
		       
		 OPEN hierarchyCursor    
		    
		 -- Perform the first fetch.    
		 FETCH NEXT FROM hierarchyCursor into @fwkDomainUserId,@ChildId    
		      
		 -- Check @@FETCH_STATUS to see if there are any more rows to fetch.    
		 WHILE @@FETCH_STATUS = 0    
		 BEGIN    
		  if (@fwkDomainUserId is not null)  
		   BEGIN  
			   Declare @moduleBitMask int 
			   Declare @moduleRoleBitMask int 

			   set @moduleBitMask =null
			   set @moduleRoleBitMask =null

			   Select @moduleBitMask = ModuleBitMask , @moduleRoleBitMask=moduleRoleBitMask from fwkApplicationuser where fwkDomainUserID=@fwkDomainUserId  
				if @moduleRoleBitMask is null
					Set @moduleRoleBitMask=0
			   if @GrantParent=1
					Begin
						if (@moduleRoleBitMask & 512=0) 
							Set @moduleRoleBitMask= @moduleRoleBitMask + 512
					End
			   if @moduleBitMask is null  
				INSERT INTO FwkApplicationUser(ApplicationBitmask,IsActiveBitmask,FwkDomainUserId,ModuleBitmask,ModuleRoleBitmask)    
				VALUES(4,4,@fwkDomainUserId,128,@moduleRoleBitMask)--Default values    
			   else if (@moduleBitMask & 128 = 0)  
				BEGIN  
				 Set @moduleBitMask=@moduleBitMask + 128  
				 Update fwkApplicationuser set ModuleBitMask = @moduleBitMask ,moduleRoleBitMask = @moduleRoleBitMask where fwkDomainUserID=@fwkDomainUserId  
				END	
			   else if (@moduleBitMask & 128 > 0 )  
				BEGIN  
				  Update fwkApplicationuser set moduleRoleBitMask = @moduleRoleBitMask where fwkDomainUserID=@fwkDomainUserId  
				END			
			END   
		Set @childId=Null
		Set @fwkDomainUserId=null
			   -- This is executed as long as the previous fetch succeeds.    
			   FETCH NEXT FROM hierarchyCursor into @fwkDomainUserId,@ChildId    
		  END    
		 CLOSE hierarchyCursor    
		 DEALLOCATE hierarchyCursor 
END 
END

GO
